0

What Excel formula can I write so it will look for the same project in Column B in Column I and place the YTD matching the project from Column J in Column C in the table below?

A            B               I         J
Name       Project         Project   YTD
Junior     7500             9100     5000
Luis       9100             7500     15000
Danny      2000             0500     1000 
Jose       1000             1000     250
Lina       0500             2000     7505
Improve this question
0

3 Answers 3

Reset to default
1

Using XLOOKUP() would be better if you have access to Excel 2021/MS365

enter image description here

• Formula used in cell C2

=XLOOKUP(B2:B6&"",I2:I6,J2:J6,"Not Found")

Suggested to use XLOOKUP() because of several reasons, here are some outlined below:

  • Exact match is the default.
  • Integer-based third argument of VLOOKUP is now a proper reference.
  • IFNA is built-in to handle missing values.
  • XLOOKUP() has no problem going to the left.
  • XLOOKUP() can do HLOOKUP().
  • It can return a cell reference if the XLOOKUP() is next to a colon such as XLOOKUP();XLOOKUP()
  • It can perform a two-way match like INDEX(,MATCH,MATCH) does.
  • It can find the last match by searching from the bottom.
  • It can find next-smaller or next-larger match without sorting the table.

The syntax of XLOOKUP() read here more from MSFT documentations. There are 5/6 examples shown as well.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Improve this answer
0

You can combine IFNA, INDEX and MATCH functions to retrive the result(s). Try the following formula:

=IFNA(INDEX($J$2:$J$6,MATCH(B2,$I$2:$I$6,0)),"")

Demo

Improve this answer
0

VLOOKUP will do what you want.

In cell C2 enter this forumula: =VLOOKUP(B2,$I$2:$J$6,2,FALSE)

Then copy that down to the rest of the cells in Col C.

enter image description here

VLOOKUP will match the value from Col B (first parameter of the forumla) to a value in Col I (the left-most column of the lookup range).

$I$2:$J$6 is the lookup range, where your lookup values are. The $ are very important as they indicate to use the same lookup range when copying the formula to cells in Col C.

The 2 in the 3rd parameter tells Excel to return the value from the 2nd column of the lookup range.

False (default for the 4th parameter) says to use an exact match.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .