Excel formula to know what shelf to put away

[ohmedic88]

Looking to write a formula that will tell someone which shelf to look for something based on the number.

I have excel 2016 and thought of conditional formatting but I run out of colors or symbols to use.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Range[/TD]
[TD][/TD]
[TD]shelf[/TD]
[/TR]
[TR]
[TD][TABLE="width: 134"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]10049541[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10057181[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10057183[/TD]
[TD]10057483[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 134"]
<tbody>[TR]
[TD]10057505

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10057762[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Shelf[/TD]
[TD]Barcode[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10049570[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10057200[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10057230[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10057702[/TD]
[/TR]
</tbody>[/TABLE]

What formula can I use to fill in the shelf if the Ranges are in Sheet 2 A2:B20 and the Shelf and Barcode is Sheet 1 A2:B20

Thanks in advance.

Eric

 

[Marcelo Branco]

Eric,

I'm a bit confused about what in your post are data (given) and where are the desired results.
Is the first table (A2:C20 in Sheet2) given and do you want the results in Sheet1 column A, being the column B the search values)?

M.