VLOOKUP not working properly

[elsuji]

Dear Team,

I am having two work books (Sample 1 and sample 2).

I need to copy data from Sample 1 (Column D & Column E) to Sample 2 (Column E & Column F) by matching Model (Column B) & M/c Sl.No (Column C).

For that i am writing VLOOKUP formula "=VLOOKUP(B4&C4,'[Sample 1.xlsx]Sheet1'!$B$2:$H$474,4,TRUE)"

But this formula is not working as per my condition. It is updating the wrong values on data's.

I am attaching the files here for your reference.

Any one help me to short out this issue.

Is there any other method for doing this also let me know

[Bob Phillips]

Try this formula

=SUMPRODUCT(--(B4='[Sample 1.xlsx]Sheet1'!$B$2:$B$474),--(C4='[Sample 1.xlsx]Sheet1'!$C$2:$C$474),'[Sample 1.xlsx]Sheet1'!$D$2:$D$474)

[elsuji]

Dear XLD,

This is not working. Some of the cells only copying from column D in Sample 2.xlsx

[Paul_Hossler]

1. VLookup ony checks the first column in the lookup range. The value that VLOOKUP wants to match is not in column B in Sample1.xlsx, only the first part is.

2. TRUE means to find the closest matching value in column B


Capture.JPG

[elsuji]

Dear Mr. Paul,

Is there any other way to copy the data's

[Paul_Hossler]

Dear Mr. Paul,

Is there any other way to copy the data's

The easiest way I know using worksheet formulas is to add a helper column to the VLOOKUP to search

Capture2.JPG


And revise the VLookup()

Capture.JPG