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 !

  • 1
    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.
    – JohnSUN
    Feb 10, 2022 at 8:46

3 Answers 3


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).


enter image description here

  • 1
    it works like a charm, thank you very much
    – olivierg
    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:


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

enter image description here


You can also use SUMPRODUCT function to get the result:


enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.