Trouble Nesting a formulae

[stewyb]

Hi Guys,

Wondering if you can help me...

I'll try my best to explain it.

I am trying to write:

If one number (1) equals another number (1) then do a vlookup. I can do that fine and if it doesn't equal the number it returns False, so I know that is right.

What I want to do is carry it on so "If one number (1) equals another number (1) then do a vlookup, if it doesn't equal that number, but equals the next number (2) then do a vlookup"

I hope that is enough info, If not let me know and I'll try and explain better

 

[jproffer]

At the end of the first IF statement, after "IF(A1=1,Vlookup, " (in the FALSE area), add:

If A1 = 2,Vlookup.......,FALSE

I don't know the particulars, so I have to speak generally, but that might lead you in the right direction.

 

[Jonmo1]

How many iterations will there be?
Which version of Excel do you have?

Can you post the 2 different VLOOKUPS that would be used based on the value of the given number?
Please post ACTUAL formulas and possible values of the number. Not Psudo simplified code.

 

[stewyb]

Thanks Guys, that was quick. I can get it to work but the IF statement only lets me use 2 iterations, is there a function that will let me use more?

Using excel 2007

The formula I am using is "=IF(D46=D2,(VLOOKUP(A48,$A$4:$I$40,4,0)),IF(D46=E2,(VLOOKUP(A48,$A$4:$I$40,5,0))))"

Thanks loads

 

[Jonmo1]

Try this assuming your iterations will be consistent..

D46 = D2 then 4
D46 = E2 then 5
D46 = F2 then 6
D46 = G2 then 7
Etc..

=VLOOKUP(A48,$A$4:$I$40,MATCH(D46,$D$2:$I$2,0)+3,0)

 

[stewyb]

Absoloutely Brilliant. Thanks for that, works like a charm

Really appreciate it!!!

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[stewyb]

Glad to help, thanks for the feedback.

I've just tried to recreate this, in another formula, was just wondering what the "+3" relates to in the formula please?

Cheers

 

[Jonmo1]

=VLOOKUP(A48,$A$4:$I$40,MATCH(D46,$D$2:$I$2,0)+3,0)

Here, the MATCH portion searches for D46 within D2:I2
it returns the position # where it was found.
If it's found in D2 - match returns 1
If it's found in E2 - match returns 2
etc..

But your formula was needing 4 if it's in D2 and 5 if it's in E2 etc..
So I added 3.

 

[stewyb]

That's brill, it worked.

Thanks a lot, learnt something new today. Appreciate it

Stew