VLookup or not to VLookup? Looking for a value in multiple tables

Hi all !

I'm new on here and would love to hear from all your geniuses

I have been set a task, and I am a little confused as to how to do it. I've tried VLOOKUP and keep getting #N/A.

The task :

I have 2 spreadsheets.

Spreadsheet One has a list of users (150) in sheet (Z), without the organisation code.

Spreadsheet two is a greater database, split into 2 sheets (X & Y), with ALL users and ALL details.

I need to get the org codes from Spreadsheet 2 (worksheets X and Y) into Spreadsheet 1 (sheet Z).


Work done so far :

I have noticed that all users in Spreadsheet One (Sheet Z) have a unique identifier called SID, a numerical field, which also correlates in Spreadsheet two (Sheets X & Y).

I have created a new worksheet in Spreadsheet one called (V).

In Sheet V, I have grabbed 2 columns, from both worksheets (X & Y) from Spreadsheet 2 ; SID and Org Code. so now my view in Codes worksheet is SID Org Code SID Org Code. (4 Columns).

I tried to add these together into one long table under Columns A and B, but the data was too much (not enough rows on Excel). so now I almost have 2 seperate tables.

I have added a new column in Spreadsheet One (Sheet Z) Called Org Code.

Here I have tried to create a V Lookup, but to no avail

Can someone please help me with this. I really don't know what I'm doing wrong.

this is the code I tried:

=VLOOKUP(B3,V!$A$3:$D$65274,2,FALSE)

B3 = Cell where the unique SID is for each user

V!$C$3:$D$65274 = Is the table that hold all the information (both SID and Org Code) in Sheet V

2 = This is where I get lost a little. I know I should put here what I want returned, but the problem is I sort of have 2 tables. And Excel seems to only allow me to select the one thing to return. So I selected Coloumn 2 (Org Code from table 1, originally grabbed from Spreadsheet Two Worksheet X)

FALSE = To confirm it returns the data to me.


I've also tried to make my table array smaller (for only 2 coloumns), but even then it doesnt work.



Can someone please please help ? I'm really at a loss as to what I'm doing wrong?

Am I even using the correction Function? (VLookup? )

thank you so much


Tiny Dancer

x

Probably you need to check 1 table then the other...

e.g.

=IF(ISNA(VLOOKUP(B3,V!$A$3:$D$65274,2,FALSE)),VLOOKUP(B3,V!$C$3:$D$65274,2,FALSE),VLOOKUP(B3,V!$A$3:$D$65274,2,FALSE))

so if it doesn't find the B3 in V! column A, then it will look for it in Column C and return what is in column D, otherwise if it is in column A, it will return what is in column B.

ISNA() checks if the result of a function is the error message #N/A.

That will be the result if the item being looked up by VLOOKUP doesn't exist in the list.

So first I checked to see if the result in NOT (i.e ISNA) in list 1. If it is NOT, then assume it is in list 2 and Vlookup in that list, otherwise assume it is in list 1 and do the Vlookup there.

Have a look at ISNA() and IF() functions in Excel help for how these functions work.

Originally Posted by tiny_dancer

Also, sorry MR NBVC, but if I understand the code,

it is saying:

IF look it up in the first table and its not there, then look it up in the second table, else look it up everywhere?

partially, but it is else look it up in the first table again (because the result of ISNA(lookup first table) was FALSE (meaning it actually does exist there).