Excel formula
 

Hi! I hope someone out here can help me.
I'm trying to make my spreadsheet for work clever!
What i want is, if a cell value, in a specified range, equals any of many
specified values from a different range, then for a seperate cell to reflect
what is in the original cell.
I don't know f this is possible but any help will be appreciated.

Thanks

Excel formula
 

Not real clear what you want. Here's my best guess.

Testing if *any* number in the range A1:A10 also appears in the range
J1:J20:

=INDEX(A1:A10,MATCH(TRUE,INDEX(ISNUMBER(MATCH(A1:A 10,J1:J20,0)),,1),0))

A result of #N/A means no numbers in A1:A10 appear in J1:J20. If more than
one number from A1:A10 appears in J1:J20 the formula returns the *first*
number that meets the condition.

--
Biff
Microsoft Excel MVP


"Stu" wrote in message
...
Hi! I hope someone out here can help me.
I'm trying to make my spreadsheet for work clever!
What i want is, if a cell value, in a specified range, equals any of many
specified values from a different range, then for a seperate cell to
reflect
what is in the original cell.
I don't know f this is possible but any help will be appreciated.

Thanks

Excel formula
 

Something like...

=IF(ISERROR(MATCH(A1, $B$1:$B$10, 0)),"-",A1)

.... will give you the contents of A1 if they matches a cell in B1:B10.
Otherwise, you'll get a dash.

- David


Stu wrote:
Hi! I hope someone out here can help me.
I'm trying to make my spreadsheet for work clever!
What i want is, if a cell value, in a specified range, equals any of many
specified values from a different range, then for a seperate cell to reflect
what is in the original cell.
I don't know f this is possible but any help will be appreciated.

Thanks

Excel formula
 

Thanks T.Valko and David Hilberg what im trying to achieve and what you have
suggested sounds alined.

I can go into a bit more detail if that will help yourselves and anyone else.
In the range A2:A90 (for e.g), there will be many product numbers for each
product.
Each product costs a different amount.
What i want is for the spreadsheet to (by me typing in the product number in
A2:A90), look in a table on the same sheet or different sheet, does not
really matter, for the corresponding price and enter that price in C2.

So by me typing in the product number in A2 i want the price to come up in
C2 auto from a table with all product numbers and prices in.

Hope this is more help

Thanks

"David Hilberg" wrote:

Something like...

=IF(ISERROR(MATCH(A1, $B$1:$B$10, 0)),"-",A1)

.... will give you the contents of A1 if they matches a cell in B1:B10.
Otherwise, you'll get a dash.

- David


Stu wrote:
Hi! I hope someone out here can help me.
I'm trying to make my spreadsheet for work clever!
What i want is, if a cell value, in a specified range, equals any of many
specified values from a different range, then for a seperate cell to reflect
what is in the original cell.
I don't know f this is possible but any help will be appreciated.

Thanks