Forum Discussion
Left Lookup Help Please
Hi
I am trying to do a left lookup between two workbooks (both stored on OneDrive for Business), however, when using the Index/Match functionality, it is returning a N/A.
I have attached the workbooks I am trying to lookup from each other
I need the formula in Column K of 'UBWO Test' to lookup the value in Column A (UBWO ID) of 'UBWO Test' in the 'Pipeline Test' workbook (value will be in Column C (UBWO ID Number) and return the value in Column A (Pipeline Request ID) of 'Pipeline Test' workbook...
3 Replies
See the attached. This formula does work, but note that the vast majority of the values in "UBWO Test" Column A, are not found in column C of the other file. That's why you're getting #NA in so many cases.
Here's the formula:
=IFERROR(INDEX('[Pipeline Test.xlsx]Client Instruction Pipeline'!$A$5:$A$94,MATCH(A2,'[Pipeline Test.xlsx]Client Instruction Pipeline'!$C$5:$C$94,0)),"Not found")
Thanks for your help - i can see that the majority of values are pulling through.
However, it just so happened then the row i was testing this on (Row 197 in the UBWO test file where the UBWO ID value is '206') is not returning the correct value, even though it is in the Client Instruction Pipeline sheet (it should be returning the value '115')....
I can't explain that. Are the others correct?
I do see that the column C data is itself (in many cases) a formula that looks things up in a table named Form1...and (having unhidden that table) that shows as a table that contains confidential/private info for sure. So please delete that workbook from this public forum.
Anyway, I think without trying to pursue this more deeply, that there must be some circularity involved somewhere. It IS clear that you're entirely competent with Excel's lookup functions. In this case, my suspicion is that the problem is more of a data design issue [i.e., the relationships between the various tables] than a simple formula could resolve. It's a suspicion, not a final diagnosis.
If most of the values are coming back correctly, then there's some anomaly involving this one ID.....and that's for you to explore.
