Lookup?

[Guest]

I have a workbook with 2 sheets. I want to compare the value in Sheet2!A1
to a range of cells in Sheet 1 (Sheet1!a1:a300). If the value in Sheet2:A1
finds a matching in the range of Sheet1!a1:a300), then the value of Sheet1!b1
should be copied to Sheet2!b1.

I'm sure there's a Lookup in there somewhere, but don't know where to start.

THANKS!

 

[Bob Phillips]

in Sheet2!B1

=IF(ISNA(MATCH(A1,Sheet1!A1:A300,0)),"",Sheet1!B1)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

This is close, but the order of the two lists are different.

In Sheet2, there is a list for product names in alphabetical order from
a6:a300.
In Sheet1, this list of products are based on category from a6:a300

So, I need to copy the value of the cell directly to the right of the list
in Sheet 1. For example:

in Sheet 2, cell A6 is the product "Cars", the cell B6 is blank
in Sheet 1, somewhere in the range of A6:a300 is the product "Cars", and in
the cell diretly to the right of that cell is either an X or a number.
I need to copy that value (the X or the number) to B6.

Thanks!!

 

[Bob Phillips]

I think this is what you want then

=IF(ISNA(MATCH(A1,Sheet1!$A$1:$A$300,0)),"",INDEX(Sheet1!$B$1:$B$300,MATCH(A1,Sheet1!$A$1:$A$300,0)))


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)