Multi Lookup

[nms2130]

I am currently using the formula below to lookup a name and its corresponding code. I type the code into cell A13. The name is fixed at B1. This formula then finds the name (B1) in a table and matches the column header of the code which I type into A13 and returns a value. This allows me to type in different codes and quickly see the value corresponding to the name. I also want to be able to type the coding into A13 and be able to see a listing of all of the names attached to that coding. Any recommendations would be appreciated.

VLOOKUP(BETA!B1,MAINTABLE,MATCH(BETA!$A13,MAIN!1:1,0),FALSE)

[NBVC]

Can you give an example of what your table may look like and what would be some results you'd expect back?

[ChemistB]

It sounds like your "Maintable" has the "names" down the left side and each column to the right of that is a different code and some "value" in each cell of each column. Each column may or may not have an entry relating to each name or, in other words, some intersections are blank and others have values. What you want is to be able to type in a code (or column heading) and get back a list of all the names that are relevent to that code (or have a non-blank intersection). Is this a good picture?

ChemistB

[nms2130]

TABLE

NAME COLOR CODE
NICK RED 98
TOM RED 98
TIM BLUE 78
TED BLUE 78
JOE RED 98
MARK BLUE 78
MAX GREEN 28
JACK GREEN 28
DAN RED 98
HARRY YELLOW 58

RESULT

RED -
NICK
TOM
JOE
DAN


I want to be able type in BLUE, RED, GREEN etc. and have a corresponding list return. I am currently able to match TED to BLUE and/or 98 but I need to be able to also pull a listing of all Names that are Blue or 98.

[NBVC]

Something like this should do it...

Please Login or Register  to view this content.

where Main!A1:A100 contains names and Main!B1:B100 contains the colour codes.. BETA!A13 is your input colour code...

Adjust ranges to suit...

Note: This is an array formula and must be confirmed with CTRL+SHIFT+ENTER not just ENTER.. you will see { } brackets appear around the formula..

Then copy it down as far as you please...

[nms2130]

IF(ROWS($A$1:$A1)>COUNTIF(Main!$B$1:$B$100,BETA!$A$13),"",INDEX(Main!$A$1:$A$100,SMALL(IF(Main!$B$1:$B$100=BETA!$A$13,ROW(Main!$B$1:$B$100)-ROW(Main!$B$1)+1),ROWS($A$1:$A1))))

Should $A$1:$A1 read Main!$A$1:$A1 and remain the blank base cell for the table?

Is B1 the header that I am using for the input in A13? I would like to type in one of the colors for instance and get a list of names to coincide.

I think this is in the right direction, I just need to understand it a little better to apply it to the actual data.

Thanks.

[NBVC]

Should $A$1:$A1 read Main!$A$1:$A1 and remain the blank base cell for the table?

ROWS($A$1:$A1) is just a counter.. no need to revise.. when copied down, the $A1 will change to $A2, $A3, etc...

Is B1 the header that I am using for the input in A13? I would like to type in one of the colors for instance and get a list of names to coincide.

BETA!A13 is where you enter the colour code and MAIN! column B contains the list of colour codes... column A contains the matching names that will be extracted where you put the formula...

If you are having difficulty, attach a spreadsheet sample here and I will add the formula for you...

[nms2130]

I have attached a sample worksheet. In looking at the sheet, I would like to type in "RED" and have the names associated with the color to be returned.

[NBVC]

See attached.

[nms2130]

It works great.

Thanks again.