Nested IF statements using VLOOKUP

[KevinE]

The following three formulas all work individually, but I can't seem to get them to work together in one fomula.

IF(AND($AE$3="A",R5<=9),VLOOKUP(AD5,$BN$162:$BO$167,2,FALSE),IF(AND($AE$3="A",R5>=10),VLOOKUP(AD5,$BN$162:$BP$167,3,FALSE)))

IF(AND($AE$3="B",R5<=9),VLOOKUP(AD5,$BN$169:$BO$174,2,FALSE),IF(AND($AE$3="B",R5>=10),VLOOKUP(AD5,$BN$169:$BP$174,3,FALSE)))

IF(AND($AE$3="C",R5<=9),VLOOKUP(AD5,$BN$176:$BO$181,2,FALSE),IF(AND($AE$3="C",R5>=10),VLOOKUP(AD5,$BN$176:$BP$181,3,FALSE)))

I am trying to return data from three small tables, based on two differing criteria for each table.

Hopefully someone can point me in the right direction.

 

[Swaroon]

How about this ?

=IF(AND($AE$3="A",R5<=9),VLOOKUP(AD5,$BN$162:$BO$167,2,FALSE),IF(AND($AE$3="A",R5>=10),VLOOKUP(AD5,$BN$162:$BP$167,3,FALSE),IF(AND($AE$3="B",R5<=9),VLOOKUP(AD5,$BN$169:$BO$174,2,FALSE),IF(AND($AE$3="B",R5>=10),VLOOKUP(AD5,$BN$169:$BP$174,3,FALSE),IF(AND($AE$3="C",R5<=9),VLOOKUP(AD5,$BN$176:$BO$181,2,FALSE),IF(AND($AE$3="C",R5>=10),VLOOKUP(AD5,$BN$176:$BP$181,3,FALSE)))))))

 

[Peter_SSs]

.. or this?

=VLOOKUP(AD5,CHOOSE(MATCH($AE$3,{"A","B","C"},0),$BN$162:$BP$167,$BN$169:$BP$174,$BN$176:$BP$181),2+(R5>=10),0)

 

[KevinE]

Swaroon & Peter, thanks very much for your time and effort, both solutions work perfectly.

Peter, your formula has definitely opened my eyes to a different approach, which is bound to be very useful in future.

 

[Jonmo1]

I know this is late, but thought yet another approach may be beneficial to the thread..

=VLOOKUP(AD5,OFFSET($BN$162,(MATCH($AE$3,{"A","B","C"})-1)*7,0,6,3),MATCH(R5,{0,10})+1,FALSE)

 

[Peter_SSs]

Kevin

As a matter of interest do the left hand columns of each table (BN162:BN167 and BN169:BN174 and BN176:BN181) all contain identical data? That is, are we looking our AD5 value up in an identical list, just in a different table each time?

 

[ShirlF]

Hi all.
Some very useful information here to a new user of nested IF statements and VLOOKUP so thankyou.

I was wondering if somebody would be able to help me? I have a sales order book to analyse on a weekly basis to get a snapshot of where we are with goods movements. I am only interested in the sales order number and changing value. So far I have used the following formula to identify which sales order number remain from the previous week with the same value: =IF(AND(E5=B5)*AND(A5=D5),"true"). This worked fine to identify where the differences immediately. Then I wrote this formula to get the values =IF(AND(A6=D6),VLOOKUP(A6,A6:B638,2,FALSE),IF(AND(A6=D6),VLOOKUP(D6,D6:E593,2,FALSE))) but the problem I have is that the sales orders are also there with matching values but not in the same rows? What I am hoping to gain from this data is to quickly see orders that are the same, new orders added, orders not shipped brought forward and any value changes due to amendments, I.E cancellations. Any help would be very much appreciated and I am able to provide the file of data if necessary. Kind regards, Shirley.

 

[Peter_SSs]

Hi all.
Some very useful information here to a new user of nested IF statements and VLOOKUP so thankyou.

I was wondering if somebody would be able to help me? I have a sales order book to analyse on a weekly basis to get a snapshot of where we are with goods movements. I am only interested in the sales order number and changing value. So far I have used the following formula to identify which sales order number remain from the previous week with the same value: =IF(AND(E5=B5)*AND(A5=D5),"true"). This worked fine to identify where the differences immediately. Then I wrote this formula to get the values =IF(AND(A6=D6),VLOOKUP(A6,A6:B638,2,FALSE),IF(AND(A6=D6),VLOOKUP(D6,D6:E593,2,FALSE))) but the problem I have is that the sales orders are also there with matching values but not in the same rows? What I am hoping to gain from this data is to quickly see orders that are the same, new orders added, orders not shipped brought forward and any value changes due to amendments, I.E cancellations. Any help would be very much appreciated and I am able to provide the file of data if necessary. Kind regards, Shirley.

I think we would need to see a small set of dummy sample data & expected results. My signature block below has suggestions for ways to do that but please do so in your own thread where you have also asked about this issue.