Solved IF then VLOOKUP and show

Microsoft Microsoft office excel 2007 ac...
July 1, 2011 at 14:12:14
Specs: Windows 7
Column "A" of Sheet 2 has SERIAL NUMBERS.

Column "B" of Sheet 2 has DESCRIPTIONS of the serial numbers.

I'm trying to show in C2 of Sheet 1, the DESCRIPTION of the SERIAL NUMBER I key in on C1 of Sheet 1.

For example:

Sheet 2:
Column A: A1=1, A2=2, A3=3 and so on...
Column B: B1=X, B2=Y, B3=Z and so on...

Sheet1:
If I key in a SERIAL NUMBER (say 3) in C1, then C2 must show the DESCRIPTION of this SERIAL NUMBER, in this case Z.

How do I do this?


See More: IF then VLOOKUP and show

Report •


#1
July 1, 2011 at 15:06:18
Per the Excel Help files (an amazing resource) the syntax for VLOOKUP is as follows:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Try:

=VLOOKUP(C1, Sheet2!$A$1:$B$100, 2, 0)

where Sheet2!$A$1:$B$100 should be changed to match the range of your actual data.

As written, this formula will return #N/A if the value in C1 is not found.

This formula will test for #N/A first and return a message if the value is not found:

=IF(ISNA(VLOOKUP(C1, Sheet2!$A$1:$B$100, 2, 0)), "Serial Number Not Found", VLOOKUP(C1, Sheet2!$A$1:$B$100, 2, 0))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 1, 2011 at 16:27:58
It worked as this:

=VLOOKUP(D15,Table3[[#All],[UPC]:[DESCRIPTION]],2,FALSE)

But now I have to tell B7 to show [ADDRESS] if B6=(A2 on Sheet named 'CLIENT MATRIX').

How can I do this?


Report •

#3
July 2, 2011 at 01:48:53
✔ Best Answer
If I'm not mistaken, this will give you the same result:

=VLOOKUP(D15, Table3[#All], 2, 0)

Since you are referencing the entire table with [#All], I don't think you need the column headers. (I'm just getting started with Tables in 2010, so feel free to correct me if I am wrong)

re: "But now I have to tell B7 to show [ADDRESS] if B6=(A2 on Sheet named 'CLIENT MATRIX')."

Is this a separate question? If so, please post it in it's own thread with a relevant subject line.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question