index match two two different columns

[josh101287]

I'm using index match but I'm trying to pull data from a cell but the look up values are in two different columns. Im trying to get it to look at one column first if they have text in it then use that data to look up the data thats needs to be matched if not then use data in a separate column. now the data in the first column that i want it to lookout is being supplied through a formula also.

[JBeaucaire]

Unclear. Please post a sample workbook that demonstrates data and the shows the kind of "match" you're trying to describe.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[josh101287]

sorry for it being unclear but i included a simple sheet that shows it. The formula would be in the G column, I'm trying to get it grab the # from the first table that is in column C and match it to the second table. i want it to first look in column F if they have data there use that as the reference to match to column B, if there is no data then use the data in column E as the reference data. I hope this clears things up a little bit.

[JBeaucaire]

Is your sample in error on the 3rd and 4th items?

One formula that will work in C2, then copied down is:

=IFERROR(VLOOKUP(B2,$E:$G,3, 0), VLOOKUP(B2,$F:$G, 2, 0))

[josh101287]

i might have explained it backwards. I would want the formula to be in Column G and to grab the number from column C. So it would look at Column f to find a match in column B if their is no match it would look at column E to find the match in Column B. But also if Column F is empty it would just go to column E to find a match in Column B. Sorry i know it is a lil confusing and i didn't explain it the best.

[shukla.ankur281190]

Try

G2=IFERROR(VLOOKUP(E2,B:C,2,FALSE),VLOOKUP(F2,B:C,2,FALSE))

Or

G2=IFERROR(INDEX(C:C,MATCH(E2,B:B,0)),INDEX(C:C,MATCH(F2,B:B,0)))

Drag down as required.

[JBeaucaire]

In t case, as Ankur has noted, the formula could be:

=IFERROR(VLOOKUP(E2,B:C, 2, 0), VLOOKUP(F2,B:C, 2, 0))


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[josh101287]

Ankur,
those formulas look great and are what I'm trying to do thanks a ton.

[FlameRetired]

Withdrawn by FR.