PDA

View Full Version : VLOOKUP not working properly



elsuji
12-07-2019, 03:19 AM
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
12-07-2019, 05:25 AM
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
12-07-2019, 09:58 AM
Dear XLD,

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

Paul_Hossler
12-07-2019, 10:28 AM
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


25572

elsuji
12-07-2019, 10:34 AM
Dear Mr. Paul,

Is there any other way to copy the data's

Paul_Hossler
12-07-2019, 12:05 PM
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

25574


And revise the VLookup()

25573