Need To Find Text And Output Corresponding Number

[Steve9383]

Hey guys, I've got a problem that I'm hoping you can help me with. I need a formula for column G. I want the formula to look at column F, match it up with the data from columns A-D and output the corresponding number.

So for example, in cell G2 I want it to look at F2 and see "a West Brom". Then I want it to search columns A-D for "a West Brom" and find it in A9. Then I want it to look at the corresponding cell (in this case B9) and output the value of the corresponding cell. So in G2, I want it to output 0.93.

Is this possible? If so, can you show me how?

Thanks in advance.

Sample Workbook.xls

[FDibbins]

Hi and welcome to the forum

try this, copied down...

=VLOOKUP(F2,$A$2:$D$21,2,FALSE)

[BB1972]

@FD, just a heads-up - I had the OPs workbook open, so copied your formula in - it's only finding/returning matches from Column A, although some of the matches are in Column C (and of course I've no idea why).

[Steve9383]

Okay I tried that and it worked for all of the "a Team Name" values but not for the "v Team Name" ones. I assumed that had something to do with my inconsistent placing (or not placing) of a period after v so I fixed it and removed all the periods but I'm still getting #N/A for those values.

[Steve9383]

@FD, just a heads-up - I had the OPs workbook open, so copied your formula in - it's only finding/returning matches from Column A, although some of the matches are in Column C (and of course I've no idea why).

I had them separated to differentiate between home and away matches. Would it help if I moved them into one column?

[BB1972]

I had them separated to differentiate between home and away matches. Would it help if I moved them into one column?

Hey Steve - absolutely - this can be solved right now if you do that.

[Steve9383]

Okay I did that and it worked. Thanks guys!

[Jakobshavn]

In G2 enter:

=VLOOKUP(F2,A$2:B$21,2,FALSE) and copy down

[popipipo]

=IFERROR(VLOOKUP(F2,A$2:B$21,2,FALSE),VLOOKUP(F2,C$2:D$21,2,FALSE))
Try to use this formula if you use excel 2007 or later.
Earlier version use :
=IF(ISERROR(VLOOKUP(F2,A$2:B$21,2,FALSE)),VLOOKUP(F2,C$2:D$21,2,FALSE),VLOOKUP(F2,A$2:B$21,2,FALSE))