Search range, find value in another col in corresponding row

  • Thread starter Thread starter Brent E
  • Start date Start date
B

Brent E

Good day,

I am trying to find a formula or VBA Code to compare a value on worksheet A
from a range of values on worksheet B. If found, assign the adjacent cell on
Worksheet A, to the value of a corresponding cell on Worksheet B.

Sample Data,
Worksheet 1 Contains
Col M ----------- Col N
1 Project 1 ------- Blank
2 Project 4 ------ Blank


Worksheet 2 Contains
Col D ------------ Col F
1 Project 1 ----- Review
2 Project 2 ----- Prototype
3 Project 3 ----- Evaluation
4 Project 4 ----- Implementation
5 Project 5 ----- Follow Up

Pseudo code:
If Cell M1 Worksheet A is found in Col D on Worksheet 2,
then N1 on Worksheet A = F1 on Worksheet B

I am using: =IsNumber(Match(WorksheetA!M1, WorksheetB!D:D, 0))
to find the cell value in the range.

I just need to figure out how to assign the data in the corresponding cell
where the match is found.

Resulting Data on Worksheet 1 should look like:
Worksheet 1:
Col M ----------- Col N
1 Project 1 ------- Review
2 Project 4 ------ Implementation

Thanks in advance
 
Try this in N1:

=IF(ISNA(MATCH(M1,WorksheetB!D:D,0)),"",VLOOKUP(M1,WorksheetB!D:F,
3,0))

then copy down.

Hope this helps.

Pete
 
Hi Brent,

I'm not sure what the IsNumber... stuff is doing but the simplest way to
hand this is to use:

=VLOOKUP(M1,Sheet2!$D$1:$F$8,3,FALSE)

Where the Project you want to lookup is in cell M1 of sheet1 and the list of
Projects and their descriptions? are in column F of Sheet2. I only addressed
a range on sheet2 running for 8 rows.

Running VLOOKUP or MATCH against entire columns may slow down Excel if in
fact you are only using the first couple of hundred or thousand rows. You
can also replace the FALSE in the formula above with 0 or even use

=VLOOKUP(M1,Sheet2!$D$1:$F$8,3)
 
That looks like that will work.Thanks Guys.

ShaneDevenshire said:
Hi Brent,

I'm not sure what the IsNumber... stuff is doing but the simplest way to
hand this is to use:

=VLOOKUP(M1,Sheet2!$D$1:$F$8,3,FALSE)

Where the Project you want to lookup is in cell M1 of sheet1 and the list of
Projects and their descriptions? are in column F of Sheet2. I only addressed
a range on sheet2 running for 8 rows.

Running VLOOKUP or MATCH against entire columns may slow down Excel if in
fact you are only using the first couple of hundred or thousand rows. You
can also replace the FALSE in the formula above with 0 or even use

=VLOOKUP(M1,Sheet2!$D$1:$F$8,3)
Click to expand...
 
Back
Top