Help on Vlookup

[REUBEN]

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

 

[Oeldere]

is your secont workbook open (CPH Relo)?

If not, that will cause a failure.

 

[REUBEN]

Thank you for your quick reply.

Yes, the second workbook is open.

what am I missing?

 

[REUBEN]

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

table A -

Table B

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.

 

[steve the fish]

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))

 

[REUBEN]

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.

 

[steve the fish]

Yes looking again the D should be F. Try that.

 

[REUBEN]

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.

 

[REUBEN]

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?

 

[REUBEN]

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"

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