# lookup for data in excel where row contains string

i have a table with numbers in Excel like this :

``````   A         B
string1    14521
string2    121
stringN    255
``````

i have another table with the same strings where i need to insert the corresponding number (e.g. 121 for string2):

``````  A          B         C
blabla     string1   <i need to place here the contents of row B in table above >
blabla     string2   <i need to place here the contents of row B in table above >
blabla     stringn   <i need to place here the contents of row B in table above >
``````

which formula should i use in cells C to do that please ? i tried a bunch of things with combinations of IF/VLOOKUP/MATCH but didn't manage to do it :( (showing the contents of cell B in table 1 for each matching string of column B)

thanks !

• Please show your failed attempt to use VLOOKUP(). It seems to me that too few parameters were specified, the last parameter was omitted, which indicates that your list is not sorted in ascending order of values in column A. Feb 10, 2022 at 8:46

That's a basic function of VLOOKUP and should be very easy to achieve. Do make sure you make the first column of your array the value you want VLOOKUP to match, and col_index_num corresponds to the column with the value you want it to produce. Fixate the array with \$\$ (or press F4), use FALSE to make sure it only finds exact matches ('approximate' matches usually give unexpected results).

``````=VLOOKUP(B8,\$A\$2:\$B\$4,2,FALSE)
``````

• it works like a charm, thank you very much Feb 10, 2022 at 9:03

You can use `INDEX` and `MATCH` functions. Assuming your tables are represented as the image below, use the formula:

``````=INDEX(\$A\$1:\$B\$3,MATCH(E7,\$A\$1:\$A\$3,0),2)
``````

Adapt the ranges to your table. Drag the formula down to fill the other cells.

You can also use SUMPRODUCT function to get the result:

``````=SUMPRODUCT((\$A\$1:\$A\$3=H1)*\$B\$1:\$B\$3)
``````