HLOOKUP-INDEX QUESTION

[MattGilbert]

I want to write a formula that matches 2 separate cell references in a range on 2 different tabs and then return the value at the bottom of the range that has that matches the 2 references. I thought it might be a combination of HLOOKUP and INDEX functions but I've had trouble getting it to work.

 

[Oaktree]

What do you mean by:

in a range on 2 different tabs

?

Can you elaborate on what you're trying to do and how the data is arranged? (e.g. what are the different ranges you want your lookup to reference?)

 

[MattGilbert]

I have serveral rows in 2 columns (separated by a few columns) on one tab of which info that I need to match in 2 rows with sevaral columns of info on another tab, and then return a value in the column 5 rows beneath the 2 rows on the 2nd tab. That's probably very difficult to follow. Maybe I can give a visual:

1st Tab
A B C D E
1 5 10 5 15 20
2 10 20 15 20 20
3 5 5 10 20 15
4 15 20 15 20 5

2nd Tab
A B C D E
1 5 10 5 15 20
2 10 10 15 20 20
3
4 100 200 300 400 500

In the above example, I need the formula to look one row [ex. A1 (5) and D1 (15)] on 1st Tab and match the values in one column [ex. C1 (5) and C2 (15)] and return the value in row 4 of that column C (300).

 

[Oaktree]

Sorry, I still don't follow you.

So, you're trying to match the 5 from table1row1 and the 15 from table1row1 into table2row1 and return table2row4?

Also, what do columns B and C do in table1? If you're going to use a lookup or a match function, the reduncancies in your numbers are probably going to cause problems (e.g. A1=C1=5)

How do you know what you're looking up in table1? (where do the 5 and 15 come from--e.g. what are their cell references?)

 

[MattGilbert]

Funny, I just read the quote at the bottom of your posts. Obviously I'm doing a poor job in that regard. It might be easier to give you the specific info. On the 1st tab, I have two columns [among several others] with info that needs to be matched. One column contains year data, the other contains notes:

Ex.
1999 95% Gross Up
1998 100% Gross Up
2003 100% Gross Up
2003 90% Gross Up
2004 95% Gross Up
1999 90% Gross Up

I have that same info in 2 rows on another tab along with an amount I want to return as the result of the lookup formula several rows below the 'notes' row:

Ex.

1999 1999 1999 2000 and so on
90% Gross Up 95% Gross Up 100% Gross Up 90% Gross Up



10.50 11.00 10.05 10.44


I want to match the year and note on 1 row on the 1st tab to the same year and note in 1 column on the 2nd tab and have the formula return the bolded number [10.50, 11.00, 10.05 or whatever] in the row a few rows below the note row.

So in the above example, the formula would match 1999 and 95% Gross Up in row 1 on the 1st tab to 1999 and 95% Gross Up in column 2 on the second tab and return the value 11.00 as the result of the formula.

Is that better? (THANK YOU for your patience here by the way)

[/img]

 

[MattGilbert]

correction - I want to match the year and note on 1 row on the 1st tab to the same year and note in THE CORRESPONDING column on the 2nd tab and have the formula return the bolded number [10.50, 11.00, 10.05 or whatever] in the row a few rows below the note row.

 

[Oaktree]

I think we're getting there.

It's a little tough to tell where your column breaks are for the second table, but I interpreted the 1999 data as being 3 rows by 3 columns, with 1999 in each of the 3 cells in the first row; the 3 cells in the second row being "90%", "Gross", and "Up" respectively; and the third row being 10.5, 11, and 10.05 respectively.

As such, I added a row to concatenate the year, %, and the words "Gross" and "Up", using =A11&" "&TEXT($A$12,"0%")&" "&B12&" "&C12, where A11 was the year, A12 the percent, B12 Gross, and C12 Up.

Then, I used =HLOOKUP(A2&" "&B2,$A$10:$L$13,4,0) to look up the Date, %, and "Gross Up" from the first table against the second table (range $A$10:$L$13 in my sheet, which is the 3 rows you had plus the concatenation as described above)

Does that help?

 

[MattGilbert]

We are getting there. Altough part of is was misunderstood because it show spaces between the columns in my posts.

On tab2, there are 2 rows with info to match - years and notes:
Years (1999, 1999, 1999, 2000, 2000, 2000, 2001, 2001, 2001 and so on)
Notes (90% Gross Up, 95% Gross Up, 100% Gross Up, 90% Gross Up, 95% Gross Up, 100% Gross Up, 90% Gross Up, 95% Gross Up, 100% Gross Up)
where the commas indicate a new cell.

Conversely, that same info is in 2 separate columns on tab 1.

However, I think I still follow you despite the miscommunication. Instead of needing to concatenate the year+%+Gross+Up, I need to conatenate year+% Gross Up in the formula for both the reference tab and the lookup tab and then do HLOOKUP to return the data located however many rows below the concatenated rows on tab2.

Am I on track?

 

[MattGilbert]

I DID IT! That's fantastic - Thanks a ton for the help. I had a little trouble with the CONCATENATE on my tab2 because I kept getting a #VALUE error even though the cells were formatted the same way where I did not get the error. I ended up just copying and pasting the cells where the concatenate was working and then just fixed the years/notes. THANKS AGAIN! HUGE HELP!

 

[Oaktree]

No worries, glad you got it to work for you!