Lookup Formula help

[Guest]

Hello,
I have a formula that results with 2 rows of data spread across 9 columns.
The first row contains text. The second a value.
(Text Text Text Text Text...
6 5 8 16 6... )
I have a column of all possible text (19) with a column beside it that
looksup the text in the first row and retrieves the value below it and MAXs'
the results.
My next column I wish it to Index the 2 rows to retrieve the same column 9
and return a different value 5 rows down. I have been attempting to use
index, match and offset IF the rows do not equal the same column with little
success. I want to know s this possible? And if so, how would I go about it?

Thank you for your help, David

 

[Guest]

Please diagram a sample of the data and the desired results.

 

[Roger Govier]

Hi David

If I understand you correctly, then you will have a series of text
values in A1:A19
You will have a series of Numbers in B1:B19

You have a series of text values in D1:L119 and you wish to retrieve the
value in row 7 where (for example) A1 and B1 match the values in D1 and
D2

If that is correct, the following array entered formula should achieve
what you want
{=INDEX($D$1:$L$19,7,MATCH(A1&B1,$D$1:$L$1&$D$2:$L$2,0))}

To produce an array formula, Commit or Edit using Control + Shift +
Enter (CSE) not just Enter.
When you use CSE, Excel will insert the curly braces { } around the
formula. Do not type them yourself.

 

[Guest]

Thank You!!! I'm just glad someone was able to decipher what I meant!
Thank YOU!
David

 

[Roger Govier]

Hi David

You're more than welcome.
Thanks for the feedback to let us know that it was a solution that met
your needs.

 

[Guest]

It did help indeed.
I have another question if you don't mind. I have a formula that provides
part of the lookup for the previous formula you so kindly helped me with and
I get the error value #VALUE! from some that don't quite meet the criteria.
Could you explain how to leave a blank cell if the error value occurs with
this formula...
{=IF(OR(EXACT(X34,$N$34:$V$34)),MAX(HLOOKUP(X34,$N$34:$V$35,2,FALSE)),"")}
Thanks again

David

 

[Roger Govier]

Hi David

Try the non-array entered formula
=IF(COUNTIF($N$34:$V$34,X34),MAX(HLOOKUP(X34,$N$34:$V$35,2,FALSE)),"")

 

[Roger Govier]

Hi David

I wasn't paying full attention in my last post.
The MAX function in your original formula is superfluous.
The Hlookup will only return the first instance of X34 within the range,
hence Max is working on a single value and doesn't achieve anything.

Change the formula to
=IF(COUNTIF($N$34:$V$34,X34),HLOOKUP(X34,$N$34:$V$35,2,FALSE),"")

 

[Roger Govier]

Hi David

Then try

=IF(COUNTIF($N$34:$V$34,X34),MAX(($N$34:$V$34=X34)*$N$35:$V$35),"")