Help on Vlookup

REUBEN

Board Regular
Joined
Mar 7, 2014
Messages
113
Hi There,

I copy information from one report to the other. The first report (A), I use as my case work tracker and have a lot of information on. But only copy few details of each case to the other workbook (B) i.e. a shared workbook used by the rest of the team on a share drive. When I copy data from one sheet to the other I simply want to auto populate the serial number of corresponding to each client's name.

Basically when I copy the case data to from, A to B a lot of details get copied on to B (including the client name column). Now what I need to autopopulate in A is the serial number corresponding to that client's name in B.

I haven't gotten this far yet, but there are instances where we may have serviced the client recently, so their name may be repeated in 'A' a few times. In that case I would only need the latest serial number from B when I paste the details of their case there from A.

I need help with the Vlookup formula to do this as my amateur skills only returned a N/A error that got me frustrated. Clearly i'm doing something wrong!

here's the formula - =VLOOKUP(D75;'[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$A$3:$F$202;1;FALSE)

Thank you.
Reuben
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Thank you for your quick reply.

Yes, the second workbook is open.

what am I missing?
 
Upvote 0
I'm enclosing the screen shots for the two tables and you would get a better idea of what I mean.

table A -
24q0rgl.jpg

Table B

5cbvxd.jpg

so you see with my previous formula - =VLOOKUP(D75;'[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$A$3:$F$202;1;FALSE)

I was trying to lookup the name of "CHANDRA, Kausik" in 'B' and find the serial number field from column 1 which in this case is '661'.

But I get an N/A error instead.

Hope I have been able to explain the problem.
 
Upvote 0
Vlookup cant help you here. It can only return values to the right of the lookup value in the lookup table. You need index match.

Think this ok

=INDEX('[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$A$3:$A$202;MATCH(D75;'[CPH RELO Reporting 2014.xlsx]IMMIG -Fragomen '!$D$3:$D$202;0))
 
Upvote 0
Hi again,

Sorry this too returned an N/A error message. I looked up the formula and noticed that the reference from $D$3:$D$202;0)) were a bit wrong as the column 1 i.e. 'A' is the correct column to start from as the serial number is there. So I even tried changing the formula to look from A instead of D, but I get the same N/A error.

 
Upvote 0
Super cool!

That did the trick.

But what if the name repeats in the sheets? Like I have explained in the thread above that there are cases where we service the same client, so their name will repeat. Will the above formula find the latest entry against that name?

thanks again.
 
Upvote 0
The next entry was a repetition and the formula gave the first instance where the name appears as the result. I want to get serial number for the last/latest entry of that name from the bottom as against the top most which the formula is providing.

Any suggestions?
 
Upvote 0
Hi All,

I have gone back to the first Vlookup formula as that works better for this requirement. However I would like the formula to express the #NA error fields with a custom text instead- "Not Found"


Code:
=IF(ISBLANK(D4);""; LOOKUP(2;1/('\\Address1);'\\Address2))
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top