This does not bring back the corresponding Matched ROW value in Column D (1 Viewer)

[dcavaiani]

=LOOKUP(B7,'C:\CollectorCards\EXCEL\Cards\[55BOFOOT_Sheet1.XLS]Sheet1'!$B$1:$B$113,'C:\CollectorCards\EXCEL\Cards\[55BOFOOT_Sheet1.XLS]Sheet1'!$D$1:$D$113)

 

[Isaac]

Can you please do the following

1. edit your formula to remove external file references, and still produce the problem
2. upload a copy of that workbook here
3. consider the following quote, from here:

Tips: Consider one of the newer lookup functions, depending on which version of Office you are using.

• Use VLOOKUP to search one row or column, or to search multiple rows and columns (like a table). It's a much improved version of LOOKUP. Watch this video about how to use VLOOKUP.
• If you are using Microsoft 365, use XLOOKUP - it's not only faster, it also lets you search in any direction (up, down, left, right).

 

[dcavaiani]

Can you please do the following

1. edit your formula to remove external file references, and still produce the problem
2. upload a copy of that workbook here
3. consider the following quote, from here:

Tips: Consider one of the newer lookup functions, depending on which version of Office you are using.

• Use VLOOKUP to search one row or column, or to search multiple rows and columns (like a table). It's a much improved version of LOOKUP. Watch this video about how to use VLOOKUP.
• If you are using Microsoft 365, use XLOOKUP - it's not only faster, it also lets you search in any direction (up, down, left, right).

I think it is because I am still using version 2002 and it has to do with the order of the data in the LOOKUP'd sheet. That should not be so difficult, but I guess it is so.

 

[Isaac]

I'm unfamiliar with version 2002, but doesn't it have vlookup? With vlookup you would specific FALSE as the final argument in the formula, which will then ensure that it searches through all of them and returns only an exact match, making the sort irrelevant.

Index/match is also a possibility, but it's hard to comment further without seeing your worksheet.

 

[dcavaiani]

I am trying to match a COL B row from 1st book to the COL B ROW(s) of another 2nd BOOK - and bring back to COL D OF the 1st book - THE COL D value in THE MATCHED COL D ROW of the 2nd book.

 

[Isaac]

I already tried your formula on my end, adjusting it for my actual ranges of data, and it seemed to work fine, meaning the next step is for you to produce the error on your end inside a workbook you can upload here for examination. But again, I and Microsoft both recommend using vlookup (if it's available in your version).

 

[dcavaiani]

I already tried your formula on my end, adjusting it for my actual ranges of data, and it seemed to work fine, meaning the next step is for you to produce the error on your end inside a workbook you can upload here for examination. But again, I and Microsoft both recommend using vlookup (if it's available in your version).

I don't get an error, just don't get the correct value returned ....

 

[Isaac]

I don't get an error, just don't get the correct value returned ....

Still, the next step would be to reproduce that situation - the one with the unexpected result - inside a workbook you can upload here. You can always dummy up the data to sanitize it for public view.

 

[arnelgp]

check the Lookup syntax, you are missing another Range (the value to return).

=Lookup(theValueToLookup, theRangeToSearch, theRangeWhereTheValueIsToBeReturned)

 

[Isaac]

OP's original formula has all 3 required arguments. It's only missing the optional 4th, the vector, which isn't required.

 

[dcavaiani]

=INDEX('C:\CollectorCards\EXCEL\Cards\[55BOFOOT_Sheet1.XLS]Sheet1'!$D$1:$D$113,MATCH(B7,'C:\CollectorCards\EXCEL\Cards\[55BOFOOT_Sheet1.XLS]Sheet1'!$B$1:$B$113,0))

Looks like this is working for me.

 

[Isaac]

Awesome, I'm happy to hear you got something working. Thanks for sharing the solution as well.