Need help with vlookup that has multiple if statements

[tlstephe]

I need to lookup an equipment number on a table and based on certain criteria bring back a value.

The formula I've tried looks like this:

=VLOOKUP(A2,Table!A:G,IF(VLOOKUP('Data to be populated'!A2,Table!A:G)="FLOAT VOLTAGE",3,IF('Data to be populated'!A2,Table!A:G)="UNINTENTIONAL GROUNDS",5,IF(VLOOKUP('Data to be populated'!A2,Table!A:G)="WATER LEVEL OK",5)," "))

I'm getting the error "You've entered too few arguments for this function".

I've attached a spreadsheet.
Data that I need populated (tab1) based on the table (tab 2)

[JieJenn]

In Excel, there's a type of formula called array formula that lets you to do a lookup based on multiple lookup values. In C2, enter
=INDEX(Table!$C$2:$C$100,MATCH(A2&B2,Table!$A$2:$A$100&Table!$B$2:$B$100,0)) and enter the formula with Ctrl + Shift + Enter keys together.

[Glenn Kennedy]

A non-array (ordinary) formula to do the same. However, I suspect the problem is more compolicated. can the equipment be tested in more than one date?? Then what happens??

In C2, copied down:
=IFERROR(INDEX(Table!C$2:C$100,MATCH(1,INDEX((Table!$A$2:$A$100=$A2)*(Table!$B$2:$B$100=$B2),0),0)),"")

similar formuale in D2 & E2.

[tlstephe]

This worked great for bringing in the Measurement Reading! Thank you!

I'm still having problems with Measurement Date and Read by. I changed the formula to:

Measurement Date: {=INDEX(Table!$F$2:$F$100,MATCH(B2&C2,Table!$A$2:$A$100&Table!$B$2:$B$100,0))}
Read by: {=INDEX(Table!$G$2:$G$100,MATCH(B2&C2,Table!$A$2:$A$100&Table!$B$2:$B$100,0))}

[Glenn Kennedy]

If your dataset is large... the array formula (especially using range concatenation) can be very slow.

[tlstephe]

I think vlookup might work better. Simply because, based on an IF statement you will bring back from different columns. Can someone help me with a vlookup and if statements?

=VLOOKUP(A2,Table!A:G,IF(VLOOKUP('Data to be populated'!A2,Table!A:G)="FLOAT VOLTAGE",3,IF('Data to be populated'!A2,Table!A:G)="UNINTENTIONAL GROUNDS",5,IF(VLOOKUP('Data to be populated'!A2,Table!A:G)="WATER LEVEL OK",5)," "))

[Glenn Kennedy]

In what way did thr solution offered at Post 3 NOT work?

[AliGW]

There don't appear to be any matches in your sample file, however I think this is what you want:

=IFNA(LOOKUP(VLOOKUP(A2,Table!$A$1:$B$100,2,0),{"FLOAT VOLTAGE","UNINTENTIONAL GROUNDS","WATER LEVEL OK"},{3,5,5}),"")

[tlstephe]

I'm getting a #NAME? ERROR using this method

[Glenn Kennedy]

Who are you talking to?

[AliGW]

If you are talking to me, IFNA is relatively new.

You could try this instead:

=IFERROR(LOOKUP(VLOOKUP(A2,Table!$A$1:$B$100,2,0),{"FLOAT VOLTAGE","UNINTENTIONAL GROUNDS","WATER LEVEL OK"},{3,5,5}),"")

[protonLeah]

If you use the same column headings in both tables then (CSE):

Please Login or Register  to view this content.

drag across and down...