lookup for data in excel where row contains string

Question

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 !

Answer 1

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)

Answer 2

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.

Answer 3

You can also use SUMPRODUCT function to get the result:

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