If(iserr(vlookup(f3,$range,3,False)),vlookup(f3,$range2,2,False),vlookup(f3,$range,3,False)
=IFNA(VLOOKUP(F1,range3col,3,FALSE),VLOOKUP(F1,range2col,2,FALSE))
It's basically the same logic, but there's 2 changes:eflamm >>>Thanks a bunch for the heads-up. I was not aware of that risk until reading your post. I would feel terrible if something I uploaded caused problems for someone so I will be careful to not do it again.
…I would never post a .xlsm file on a public board - anyone who loads it could get zapped by the code…
Please disregard the fact that each of the 2nd-column values in the table not only contains exactly three letters but the first three letters of the corresponding 1st-column values. That is just a coincidence of this example but does not apply where I plan to use the solution.
Eric Flamm >>Thanks for the feedback Eric but I cannot get this formula, even when completed, to return a meaningful result.
I would use something like:
If(iserr(vlookup(f3,$range,3,False)) ,vlookup(f 3,$range2, 2,False),v lookup(f3, $range,3,F alse)
Eric Flamm >>This formula returns a value from the 3rd column but the objective is to return values from both the 2nd & 3rd columns.
Well - I went and did a test, and found this option:
=IFNA(VLOOKUP(F1,range3col,3,FALSE), VLOOKUP(F1 ,range2col ,2,FALSE))
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Excel - Data Validation | 3 | 28 | |
Request to review costing formula | 3 | 35 | |
Return Column Number based on a specific value | 25 | 26 | |
How to make data for pivot table open and add to original sheet with the named sheet | 16 | 29 |
Join the community of 500,000 technology professionals and ask your questions.