Avoiding Nesting Multiple Vlookups / One Value against changing Array

[kevt97]

Excel 2010

I have this problem I cannot figure out. I generally use Vlookups.

My lookup value is in cell A4, this equals $1538.00.

I want to check a dataset for this value.

The dataset =

col1
$1,538.46 $1,538.00 $1,538.47 $1,538.45 $39,999.96

Basically the value of A4 can vary up to x variations - this is in my table.

what I need:

I want to check A4 against col1, then col 2, then col 3 etc. and return col 5 when any of these cols match. if not, then 0.

 

[Peter_SSs]

Re-reading the thread, this isn't much different to nested VLOOKUPs that you wanted to avoid.

In fact, the VLOOKUP is shorter in length.

=IFERROR(INDEX(H2:H5,IFERROR(MATCH(A4,D2:D5,0),IFERROR(MATCH(A4,E2:E5,0),IFERROR(MATCH(A4,F2:F5,0),IFERROR(MATCH(A4,G2:G5,0),-1))))),0)
=IFERROR(VLOOKUP(A4,D2:H5,5),IFERROR(VLOOKUP(A4,E2:H5,4),IFERROR(VLOOKUP(A4,F2:H5,3),IFERROR(VLOOKUP(A4,G2:H5,2),0))))

 

[mikerickson]

If your data is in D1:I4 and 1538.00 is in A4, the CSE formula
=MIN(IF($D$1:$I$4=A4,$H$1:$H$4))

should return those values.
It needs to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[Peter_SSs]

Since you are using Excel 2010, here is another option.
It avoids nesting and would be easily scalable to more columns (and is shorter).

Excel Workbook
	A	B	C	D	E	F	G	H
2				1,538.46	1,538.00	1,538.47	1,538.45	39,999.96
3				1,538.46	1,538.00	1,538.47	1,538.45	39,999.96
4	1,653.86	43,000.10		1,653.85	1,654.00	1,653.86	1,653.84	43,000.10
5				1,711.54	1,712.00	1,711.55	1,711.53	44,500.04
Lookup

 

[Peter_SSs]

If your data is in D1:I4 and 1538.00 is in A4, the CSE formula
=MIN(IF($D$1:$I$4=A4,$H$1:$H$4))

should return those values.
It needs to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

Very concise Mike & it may well be what the OP wants. If so, I had the wrong interpretation as I thought the search for the A4 value needed to be in columns from left to right. So for the data below, I thoght the value would need to be 41,000. Have to wait for OP feedback I guess.

Excel Workbook
	A	B	C	D	E	F	G	H	I
2				1,538.46	1,538.00	1,538.47	1,538.45	41,000.00	2,014.00
3				1,538.46	1,538.47	1,538.00	1,538.45	39,999.96	2,013.00
4	1,538.00	41,000.00		1,653.85	1,654.00	1,653.86	1,653.84	43,000.10	2,012.00
5		39,999.96		1,711.54	1,712.00	1,711.55	1,711.53	44,500.04	2,011.00
Lookup

 

[mikerickson]

The formula that I gave will given the mimimum matching value.

To get the first row that matches, this CSE would work

=INDEX(resultColumn, MIN(IF(dataRange=A4, ROW(dataRange))))