Function lookup match index all together

[jeanphilip16]

Really need your help!

Below is an short list of data but mine as more than 1000 rows:
State Mobile Buffalo Marshfield
AL 36 1102 716
NY 166 36 419
WI 238 888 36

How can I create a function that will search the city in the title with vatiable states and numbers, then variable rows and columns...
Exemples:
AL & 36 =Mobile
NY & 36 =Buffalo
WI & 36 =Marshfield

Thanks for your time!

[NBVC]

something like:

=INDEX($B$1:$D$1,MATCH(36,INDEX($B$2:$D$1000,MATCH("AL",$A$2:$A$1000,0),0),0))

where data is in A1:D1000 (including column and row headers). And you can replace the 36 and "AL" with cell referencing containing those criteria.

[jeanphilip16]

Exactly what I was looking for!!!!
Thank you soooo much.

Also, do you have a formula that will find the minimum (36) in a specific row only if the title contains a specific word?

Exemple:
Search "AL" in the first column
Search "M" in the title

I did a Min(if) but maybe there is a better way to do it?

{=MIN(IF((A$1:A$2000="AL")*(ISNUMBER(SEARCH("M",B$1:Z$1))),B$2:Z$2000))}

[NBVC]

There is more than one column header with "M" in it..... do you need to check all? Also, does it only have to start with M, or even if m is in the word, like Tampa?

[jeanphilip16]

Yes, there will be more than one column header with "M" in it.

The "M" could be everywhere in the header. (In reality the "M" in a word)

Thanks!

[NBVC]

Something like:

=MIN(IF(ISNUMBER(SEARCH("M",$B$1:$Z$1)),INDEX($B$2:$F$2000,MATCH("AL",$A$2:$A$2000,0),0)))

confirmed with CTRL+SHIFT+ENTER not just ENTER

[jeanphilip16]

Everything works!!!!

Thanks

[jeanphilip16]

Need again your help...

Need to find a value in a table with 2 search in the first 2 rows and 1 match in the first column.

LM PP PN
CB BX BX
AL 36 11 71
NY 16 36 41
WI 23 88 36

Search PP & BX + Match WI = 88

Thanks!!!

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F	G	H	I
1		LM	PP	PN		PP	BX	WI	88
2		CB	BX	BX
3	AL	36	11	71
4	NY	16	36	41
5	WI	23	88	36

This array formula** entered in I1:

=INDEX(B3:D5,MATCH(H1,A3:A5,0),MATCH(G1,IF(B1:D1=F1,B2:D2),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[jeanphilip16]

I forgot to mention that in the first 2 rows the text could be larger than requested...

-- LMPN PPBD PN
-- CBFL BX - BX
AL 36 - 11 - 71
NY 16 - 36 - 41
WI 23 - 88 - 36

Search PN & CB + Match WI = 23

[Tony Valko]

Try this array formula**:

=INDEX(B3:D5,MATCH(H1,A3:A5,0),MATCH(TRUE,ISNUMBER(SEARCH(F1,B1:D1)+SEARCH(G1,B2:D2)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[jeanphilip16]

Another one...

=INDEX($B$1:$D$1,MATCH(36,INDEX($B$2:$D$1000,MATCH("AL",$A$2:$A$1000,0),0),0))

In this formula, how can I add a search for a specific word in $B$1:$D$1 that contains text in each cell?

[Tony Valko]

Don't understand.

Show an example including the result you expect.

[jeanphilip16]

St - MLL - BFF - BLL
AL - 155 - 999 - 999
NY - 166 - 136 - 419
WI - 238 - 888 - 236

Match AL in the first column, Match 999 in the line, search L in the first line, return the text "BLL"

Thanks!!

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F	G	H	I
1	----	MLL	BFF	BLL	----	AL	999	L	BLL
2	AL	155	999	999
3	NY	136	136	419
4	WI	238	888	236

Array entered**:

=INDEX(B1:D1,MATCH(1,IF(INDEX(B2:D4,MATCH(F1,A2:A4,0),0)=G1,IF(ISNUMBER(SEARCH(H1,B1:D1)),1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[jeanphilip16]

It works again!!!
Thanks

[Tony Valko]

You're welcome. Thanks for the feedback!