Hi,
Looking for the function formula to do:
User enters X
User enters Y
Excel looks up the first row for X
then looks down the first column for Y
Then returns the value where they intersect
I tried VLOOKUP and DGET ,
=HLOOKUP(J17,Database!1:1048576,MATCH('Raw Data'!J18,Database!A:A,0),TRUE)
MATCH is retunign the row #- not sure that is correct
J17=X
J18=Y
Many thanks
Are you looking for something like this example...
http://contextures.com/xlfunctions03.html#IndexArg
HTH
Regards, Jeff
Sorta, i only see a way to enter 1 value, not 2 in the index function
Does this help?
Correction, I did =INDEX(Database!1:1048576,'Raw Data'!J17,'Raw Data'!J18)
but it returns the wrong value
Ok I tried your formula- it returns N/A
=INDEX(Database!1:1048576,MATCH('Raw Data'!$J$17,Database!1:1,0),MATCH('Raw Data'!$J$18,Database!A2:A802,0))
It picks up the first match
second match makes itN/A
value is there
It's time to attach a workbook sample please with your setup and what you desire
here is the file- enter in 2.05 in J18
Your database is a sheet yes? and instead of using row/columns like usual you have column 1 and row 1 containing x and y values.
=HLOOKUP("[xvalue]",Sheet1!1:[maxrows],MATCH("[yvalue]",Sheet1!A:A, 0),TRUE) - which is what you have
should return the value at the intersection (does for me anyway!)
Match returns an offset from the given range, so if you provided A5:A10 and your value was in A6 the result would be 1 (i think <_<), but as you are using A:A, it is a row number anyway.
Not sure what your problem is? have you tried evaluating the formula to see what's going on?
---
edit: I need to type fastermissed a good few responses
looking at your database sheet, there is no value at 400/2.05 (400 being the value already in there)
Last edited by NickSlash; 02-06-2012 at 11:04 PM.
Hi
=HLOOKUP(J17,Database!1:1,MATCH(J18,Database!A:A, 0),FALSE)
yields #REF!
See this example...
The formula you had in column A was not allowing the match and your references were slightly off, but also there is no intersect for 400 and 2.05. I put a number in that intersect just for testing
Hi Jeff,
GREAT!!!!
That works- was the issue absolute references?
You could also change the Match type argument in your Match() function to not be an exact match
=HLOOKUP(J17,Database!1:1048576,MATCH('Raw Data'!J18,Database!A:A,1),FALSE)
No, the issue was in column A Database, the match just was not liking the formula you had starting in A3. Instead, I right clicked on the fill handle and drag down and when released, fill series with step value of .01
You also had your row reference and column reference backwards. =INDEX(array,row_num,column_num)
Hi I am back yet again, it worked last night- woke up and now I get N/A again for a valid Row
=MATCH('Raw Data'!$J$18,Database!$A$2:$A$902,0)
Yields N/A for 2.04
I changed it to
=MATCH('Raw Data'!$J$18,Database!$A$2:$A$902,1)
Now it works
1
The Match function will find the largest value that is less than or equal to value. You should be sure to sort your array in ascending order.
If the match_type parameter is omitted, the Match function assumes a match_type of 1.
In the sample I posted, 2.04 works for me. In this case, I think you want to use 0 because aren't you looking for an exact match? As a test, in any cell select = and then point to J18 with the 2.04 and then point to 2.04 in the Database tab A2:A902. If they match you will get TRUE, but if not, FALSE.
Again, seems to me you are doing an exact match so if you return FALSE I would investigate why. Could it be the number is actually stored as text and not a real number thus the #N/A.
To fix this, place a 1 in any open cell. Copy the cell >> highlight Database A2:A902 >> right click >> paste special >> multiply >> OK
Also, you can sometimes just look at the cell and tell. Text aligns left while numbers align right
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks