LOOKUP?

[Guest]

I have a workbook containing 2 worksheets.
The first sheet is setup in rows, and contains a unique code in column A,
then lots of other data in several columns.
The second sheet is a 'Summary' page. The idea is that I want to enter a
code ( as found on the first sheet ), some kind of LOOKUP is performed to
match the code with the first sheet, then have other cells on the summary
sheet populated by the relevant columns as specified from the first sheet.
For example :
Data sheet contains five colums: A: Code, B:Name, C:Address, Dhone, E:Email
Each row then contains the data 002 FRED 12 High St 020 345
fred@high
My second sheet has a cell where I enter my Code
It also has 2 cells for NAME and PHONE that I want to populate from the
first worksheet.
I tried VLOOKUP, but this doesn't work - as some rows in column a are empty
and I cannot sort the data either.
Any ideas?
Thanks.

 

[Guest]

Why doesn't VLOOKUP work?

=IF(ISNA(VLOOKUP("002",Sheet1!$A:$E,2,0)),"",VLOOKUP("002",Sheet1!$A:$E,2,0))will return Name or blank (empty) if no code match

=IF(ISNA(VLOOKUP("002",Sheet1!$A:$E,4,0)),"",VLOOKUP("002",Sheet1!$A:$E,4,0)) will return Phone or blank (empty) if no code match

HTH

 

[Bondi]

I have a workbook containing 2 worksheets.
The first sheet is setup in rows, and contains a unique code in column A,
then lots of other data in several columns.
The second sheet is a 'Summary' page. The idea is that I want to enter a
code ( as found on the first sheet ), some kind of LOOKUP is performed to
match the code with the first sheet, then have other cells on the summary
sheet populated by the relevant columns as specified from the first sheet.
For example :
Data sheet contains five colums: A: Code, B:Name, C:Address, Dhone, E:Email
Each row then contains the data 002 FRED 12 High St 020 345
fred@high
My second sheet has a cell where I enter my Code
It also has 2 cells for NAME and PHONE that I want to populate from the
first worksheet.
I tried VLOOKUP, but this doesn't work - as some rows in column a are empty
and I cannot sort the data either.
Any ideas?
Thanks.

Hi,

I'm not sure but maybe you can enter FALSE as keyword in the
Range_Lookup property of your VLOOKUP function.

Regards,
Bondi

 

[Guest]

Sorry for being a dumb arse, but I have very little Excel knowledge and don't
really understand your answer.
First, my lookup value is going to be entered into a cell on the 2nd
worksheet - so can I just substitute StoreSummary!A3 for where you have "002"
?

I only want the lookup performed on column A - where you have Sheet1!$A:$E -
I guess I alter mine to Tracker!$A:??

Column A - where the lookup is performed only has an entry every 3 cells, 2
blank rows between each entry. Also, it isn't sorted - so the numbering is
random. It has to stay this way. I thought these two facts would stop VLOOKUP
from working?

Thanks for the help so far.

 

[Guest]

You are right:

=IF(ISNA(VLOOKUPStoreSummary!A3
Tracker!$A:$E,2,0)),"",VLOOKUPStoreSummary!A3 Tracker!$A:$E,2,0))

As we are trying to retrieve data from several columns (A to E) we tell
VLOOKUP that is the range of data with the "search" always done on the first
column, in this case A. The number after the range i.e. 2 in the example
above, tells VLOOKUP to get the data from the second column in the range i.e.
Column B which contains the NAME.

So we find the row for code "002" and get the value from Column B to return
the NAME (Fred). The 'phone number is in column D so we set the value in
VLOOKUP to be 4 (4th column in the range A:E).

=IF(ISNA(VLOOKUPStoreSummary!A3
Tracker!$A:$E,4,0)),"",VLOOKUPStoreSummary!A3 Tracker!$A:$E,4,0))

The last parameter in VLOOKUP (0) can also be specified as FALSE which tells
VLOOKUP the data is NOT sorted.

And finally, having blank rows does not matter.

Does this help?

 

[Guest]

Help - Yes, Solve - No.

So........ to simplify if I enter this formula
=VLOOKUP(StoreSummary!A3,Tracker!$A:$BO,2,0) into a cell

....shouldn't this take the 'value' from the cell A3 on the Store Summary
sheet, compare it to the entries in column A on the Tracker sheet, find a
match then return the data from the 2nd column in the matched row?

All I get is #REF!

Thanks again

 

[Guest]

You usually get #REF error if the sheet names are wrong e.g StoreSummary
instead of "Store Summary". I created a w/book with sheet "Trackers" rather
than "Tracker" and had a #REF error.

Otherwise worked OK.

I would leave the error checking in the formula otherwise you will get a
#N/A error. if there is no code in your Tracker table.

 

[Guest]

Got it sorted now - the problem was I'd included the sheet name with the cell
reference on the same sheet where the formula was - took the name out and it
works like a dream!

Thank you Toppers

 

[Guest]

Glad it's working and thanks for the feedback.

Got it sorted now - the problem was I'd included the sheet name with the cell
reference on the same sheet where the formula was - took the name out and it
works like a dream!

Thank you Toppers