Lookup value in list and return value from cell?

[ledetekst]

Hi all.

Probably a very simple question. Have an order form, that needs the following.

On sheet 1. In column A (called ItemNumber), I have the item numbers. In column B (called ItemName), I have the item names.

On sheet 2. I have a data validation list in column A, that refers to ItemName from sheet 1.

What I want, is to return the value from sheet 1 column B in sheet 2 column B. When I select an ItemName from the list.

Cheers

 

[VoG]

Try VLOOKUP, e.g.

=VLOOKUP(A1,Sheet1!A1:B100,2,FALSE)

Tutorial http://www.contextures.com/xlFunctions02.html

 

[JamesW]

Hi there,

Welcome to the board.

Not sure I fully understand. You have ItemNumber in Col A (Sheet1), ItemName in Col B (Sheet1) and you want to return the ItemName when you select the ItemName?

Do you mean you want to return the ItemNumber when you select the name, or return the ItemName when you select the Number?

 

[ledetekst]

Hi folks!

Sorry. Typo! Meant that I want to return the value from sheet 1 column A, in sheet 2 column B. So that the ItemNumber is returned, when I select the ItemName.

Also, am running scandinavian version of Excel (2003), so I have to use semi colon ";" instead of comma ","

Using the formula VoG gave, it returns #N/A
(=FINN.RAD(A1;'Sheet1'!A1:B100;2;USANN)

 

[VoG]

Try

=INDEKS(Sheet1!A:A;SAMMELIGNE(A1;Sheet1!B:B;0))

 

[ledetekst]

Managed to get it to work.
But by moving (switching place) ItemName to column A and ItemNumber to column B in sheet 1, and also setting value FALSE in formula (so that no invalid values can't be set), it worked

=FINN.RAD(B1;Sheet1!$A$1:$B$1000;2;USANN)