Avoiding Nesting Multiple Vlookups / One Value against changing Array

K

kevt97

New Member
Joined
Jan 5, 2007
Messages
7
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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Like this?

I have the A4 value occurring 3 times in the lookup table. The formula finds the first column containing the A4 value and then uses the first occurrance in that column.

Excel Workbook
ABCDEFGH
1
21,000.001,001.001,538.001,003.005,000.00
31,538.461,538.001,538.471,538.4539,999.96
41,538.0039,999.961,600.001,538.001,540.001,541.0050,000.00
5
Lookup
 
Upvote 0
Peter_SSs said:
Like this?

I have the A4 value occurring 3 times in the lookup table. The formula finds the first column containing the A4 value and then uses the first occurrance in that column.

Excel Workbook
ABCDEFGH
1********
2***1,000.001,001.001,538.001,003.005,000.00
3***1,538.461,538.001,538.471,538.4539,999.96
41,538.0039,999.96*1,600.001,538.001,540.001,541.0050,000.00
5********
Lookup
Click to expand...

not exactly...assume the variations of A4 are in one row of a larger table. each row is a different reference value with multiple variations.
 
Upvote 0
kevt97 said:
not exactly...assume the variations of A4 are in one row of a larger table. each row is a different reference value with multiple variations.
Click to expand...
I don't really understand that. Could we have small set of sample data and expected results?
 
Upvote 0
Peter_SSs said:
I don't really understand that. Could we have small set of sample data and expected results?
Click to expand...

biweekly round 0.01 (0.01) annual class
1,538.46 1,538.00 1,538.47 1,538.45 39,999.96 2,014.00
1,538.46 1,538.00 1,538.47 1,538.45 39,999.96 2,013.00
1,653.85 1,654.00 1,653.86 1,653.84 43,000.10 2,012.00
1,711.54 1,712.00 1,711.55 1,711.53 44,500.04 2,011.00

want to check A4 against biweekly col, then against round col, then against 0.01 col etc. and in each case return annual salary. I would like to do this without nesting vlookups.
 
Upvote 0
mikerickson said:
From the data in post #5 and the input 1538.00, what result do you want?
Click to expand...

39,999.96

as ex:

if input = any of these 1,653.85 1,654.00 1,653.86 1,653.84 then result = 43,000.10

basically the input can be up to 4 variations of the value in the input cell
 
Upvote 0
In what way does my previous formula not do that?

Excel Workbook
ABCDEFGHI
21,538.461,538.001,538.471,538.4539,999.962,014.00
31,538.461,538.001,538.471,538.4539,999.962,013.00
41,653.8643,000.101,653.851,654.001,653.861,653.8443,000.102,012.00
51,711.541,712.001,711.551,711.5344,500.042,011.00
Lookup
 
Upvote 0
Seems like it works perfect. My apologies.

However, I don't quite understand how it works. It is clearly passing row and column data to an index function but can you explain it further.

Thank you, again!
 
Upvote 0
kevt97 said:
Seems like it works perfect. My apologies.

However, I don't quite understand how it works. It is clearly passing row and column data to an index function but can you explain it further.

Thank you, again!
Click to expand...
Re-reading the thread, this isn't much different to nested VLOOKUPs that you wanted to avoid. :)

For my layout, the first MATCH function looks for the A4 value in column D. If found it returns the position down column D & then the INDEX function returns the corresponding value from column H.

If the first match fails, an error is generated so the IFERROR sends the formula to the second MATCH function which repeats the above but for column E.

etc

until the final MATCH, which if it fails I've forced another error by trying to return the value from the -1<sup>th</sup> position in column H. This triggers the very first IFERROR in the formula & that IFERROR returns 0 as requested
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Check date is is a range and return value of period
Replies
6
Views
2K
JonRowland
J
J
  • Solved
Using a UDF with a Vlookup formula
Replies
2
Views
535
julhs
J
A
  • Question
Nested If + If Error + Vlookup formula help needed
Replies
1
Views
588
aravindhan_31
A
F
  • Solved
How to simplify a nested "IF" formula with a test & reference to multiple VLOOKUP tables?
2
Replies
15
Views
1K
DanteAmor
DanteAmor
M
  • Solved
Need to assess multiple values from an array, maybe a VLOOKUP, not really sure!
Replies
8
Views
351
MyGConnorD
M

Forum statistics

Threads
1,214,954
Messages
6,122,462
Members
449,085
Latest member
ExcelError

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top