Hi,

I've been trying to work out a way to use one of the LOOKUP functions (or possible INDEX/MATCH) which allows me to select multiple lookup values from a list, and then search adjacent columns to where the formula sits and return the first value it finds from the list.

To paraphrase my setup...

TABLE A

Fruit
Orange
Apple
Banana
Pear

TABLE B
Col A is where the formula sits
Col B is a list of dates
Col C - G are a list of different workers

Then the cells in table B are populated (in this example) with values validated from a list which references Table A i.e. I can apply a fruit to a worked on a certain day.

I want the formula in Col A to reference the values in Table A as a lookup, then search contents of the cells in the same row, columns C thru G for one of these values. When it finds one it returns that value into the cell with the formula in.

I've tried XLOOKUP, VLOOKUP and INDEX MATCH but I can't work out a way to have multiple lookup values and for it to return the value it finds.

Help much appreciated!

Tim