Looking for VLOOKUP Trick

[shdawson]

Hi,

Love VLOOKUP. Has met a lot of needs I have had over the years.

Question:
What do you do when the lookup value is to the RIGHT of the lookup range? In the past, I have referenced the row via =B2, say in column Z. It works, but it can get messy.

Is there another trick out there to overcome this problem?


Thanks,
S

 

[schielrn]

What version of Excel are you using? If you are using 2007 or newer you can probably just use:

=iferror(INDEX('MSP_Export - LAST Time'!N$2:N$10000,MATCH(1,IF('MSP_Export - LAST Time'!A$2:A$10000=D2,IF('MSP_Export - LAST Time'!B$2:B$10000=F2,1)),0)),"New Task")

If using an older version, you will need something like this, which will be much slower:

=if(isnumber(MATCH(1,IF('MSP_Export - LAST Time'!A$2:A$10000=D2,IF('MSP_Export - LAST Time'!B$2:B$10000=F2,1)),0)),INDEX('MSP_Export - LAST Time'!N$2:N$10000,MATCH(1,IF('MSP_Export - LAST Time'!A$2:A$10000=D2,IF('MSP_Export - LAST Time'!B$2:B$10000=F2,1)),0)),"New Task")

Hope the parentheses are right.

 

[shdawson]

Excel 2002. (I added that to my auto-signature for future simplicity.)

WOW! You weren't kidding about that take a while part. Especially with 5,700 records.

Works great, thanks! Have a great day!!!

 

[schielrn]

Excel 2002. (I added that to my auto-signature for future simplicity.)

WOW! You weren't kidding about that take a while part. Especially with 5,700 records.

Works great, thanks! Have a great day!!!

So is it 2002 or 2003? Yea, it basically has to do the match twice in the older versions, which is why it is so slow.

 

[Gerald Higgins]

I just found this thread - the CHOOSE function for looking up left is indeed awesome, well done Barry.

I too was just playing around with it, and I noticed a couple of things.

First, all the examples I've seen show ranges aligned and on the same sheet, but they don't have to be.
This also works

=VLOOKUP(A2,CHOOSE({1,2},D11:D21,Sheet2!B1:B11),2,FALSE)

Second, it also works if the lookup ranges are not the same size.
For example, this works

=VLOOKUP(A2,CHOOSE({1,2},D11:D21,Sheet2!B1:B101),2,FALSE)

It effectively ignores values in Sheet2!B22:B101

This also works

=VLOOKUP(A2,CHOOSE({1,2},D11:D21,Sheet2!B1:B2),2,FALSE)

AS LONG AS the target value is found in D11:D2, otherwise it will fail.

This might have practical applications if you could not be certain how large the (D11:D21) range was.

I'm not sure of this gives any functionality that Index / Match don't, but it's still fun!

 

[shdawson]

Quaint.


This has opened a whole new bag of tricks. INDEX and MATCH are indeed very useful.


S

 

[shdawson]

Hi,


Need to see if we can do the "mirror image" of this thing, please.

I have a bunch of data on worksheet 1.
I have a lookup table on worksheet 2.
I need to add some lookup fields on workhsheet 1.
I need to lookup a value, based upon multiple critera.


I need to...
look in field 2 and field 3 on worksheet 1,
find the match in field 2 and field 3 on worksheet 2,
then.....
populate field 1 on worksheet 1 with field 1 on worksheet 2.





Do I need to expand on the description?


Thank You,
SHD

 

[Matty]

Sounds like the following in A2 on Sheet1:

=INDEX(Sheet2!field1,MATCH(1,IF(Sheet2!field2=B2,if(Sheet2!field3=C2,1)),0))

Array entered, i.e. with CTRL+SHIFT+ENTER, where field1, field2 and field3 are all ranges.

Matty

 

[shdawson]

Matty,


Hello. Thanks for the input! Your suggestion looks like it is what I am after.

Current code:

=INDEX('[Driver Table.xls]Detail'!$I$2:$I$1000,MATCH(1,IF('[Driver Table.xls]Detail'!$F$2:$F$1000=D2,IF('[Driver Table.xls]Detail'!$D$2:$D$1000=G2,1)),0))

This worksheet is playing up. The column does not want to take the formula. I did the Menu/Data/Text to Columns thing and still does not like it. The cell displays the formula versus calcuating the formula.

Also, when I highlight the formula in the address bar, then applied C-S-E, it does not put the curly brackets around the formula. Probably related to the problem stated above.

FYI, this is a text and number combo evaluation (if that matters)

So, how does what appears to be a worksheet problem get resolved, please?


Thanks,
SHD

 

[shdawson]

Hmmmmmm.....


Looking over the Show Calculation Steps. The problem is the MATCH part.

This is where the error shows in the step's:

=INDEX('[Driver Table.xls]Detail'!$I$2:$I$1000,#N/A)

Perhaps the worksheet is fine and the MATCH is the problem......?



SHD

 

[Matty]

=INDEX('[Driver Table.xls]Detail'!$I$2:$I$1000,MATCH(1,IF('[Driver Table.xls]Detail'!$F$2:$F$1000=D2,IF('[Driver Table.xls]Detail'!$D$2:$D$1000=G2,1)),0))

Syntactically, that looks OK. Are you sure the combination of D2&G2 exists in ranges F2:F1000&D2:D1000?

Could you post a small example of your data? This will make it easier to diagnose the issue.

Matty