=IF and Vlookup formula - mind blank

  • Thread starter Thread starter bill
  • Start date Start date
B

bill

I can't get my head around what must be a routine issue
even though using
http://www.mvps.org/dmcritchie/excel/vlookup.htm

I will have 2 tables on 2 worksheets of a Work Book.
Worksheet 1 titled "Scanner Data" has a table occupying
A3:B89 called Scanner_table
which contains (in column A) scanner names &
in column B,) that scanner's serial number.
Cells A2 & B2 contain the title of each column

Worksheet 2 titled Fault Log has a log into which staff
should key in a (in Column A) cell, the Scanner name.
In the equivalent column B cell, that scanner's serial
number should automatically appear when column A data is
populated.

I need a formula in B2 that will give the serial number
when the scanner name is entered in A2

I created an =if formula as a stop gap for our initial
group of 6 scanners- it works fine but will be
impractical when all 90 + scanners appear.

=IF(C4=A4,"M1H28M71L",IF(C4=A5,"M1H28P12F"))(etc etc)

Suggestions would be gratefully received
Thanks & regards
Bill
 
bill wrote...
...
I will have 2 tables on 2 worksheets of a Work Book. Worksheet
1 titled "Scanner Data" has a table occupying A3:B89 called
Scanner_table which contains (in column A) scanner names &
in column B,) that scanner's serial number. Cells A2 & B2 contain
the title of each column

Worksheet 2 titled Fault Log has a log into which staff should
key in a (in Column A) cell, the Scanner name. In the equivalent
column B cell, that scanner's serial number should automatically
appear when column A data is populated.

I need a formula in B2 that will give the serial number when the
scanner name is entered in A2
Click to expand...
...
=IF(C4=A4,"M1H28M71L",IF(C4=A5,"M1H28P12F"))(etc etc)
...

If you or your users enter scanner names in cell C4 on the secon
worksheet and want the corresponding scanner serial number to appear i
cell D4 in that worksheet, try the following formula in cell D4 of th
second worksheet.

=IF(TRIM(C4)="","",VLOOKUP(C4,Scanner_Table,2,0)
 
Back
Top