Cross referencing

[Mervyn Edwards]

I am trying to pull the information from the cell at the intersection between
a particular row and column. The identifier for which row and column needs to
come from an input value. I have tried various incarnations of vlookup and
hlookup with match, but cannot get a result.

Example: Input Values would be 200 and FL310 to establish an answer of 177

FL300 FL310 FL320
100 180 178 176
200 179 177 175
300 175 174 172

Any help would be much appreciated.

 

[Mike H]

Mervyn

Maybe:-
Col A B C D
FL300 FL310 FL320
100 180 178 176
200 179 177 175
300 175 174 172


=SUMPRODUCT((A2:A4=F1)*(B11=F2)*(B24))

Where f1 & F2 are your 2 input values.

Mike

 

[Teethless mama]

Criterias
F1: holds 200
F2: holds FL310

Pick any one of these formulas will do the trick

=INDEX(A14,MATCH(F1,A1:A4,0),MATCH(F2,A11,0))
or
=HLOOKUP(F2,A14,MATCH(F1,A1:A4,0),0)
or
=VLOOKUP(F1,A14,MATCH(F2,A11,0),0)

 

[Mervyn Edwards]

Hi,

This example is exactly how I was trying to get v and hlookup to work, but I
always receive an error saying "value not available". I have looked at what
can cause the error but none of the suggested reasons exist on the
spreadsheet. Weird!

Thanks
Mervyn

 

[Stephen]

These formulas all work for me. The usual reason for such formulas
apparently not working is different types for lookup value and table data.
In the example below, the 200 in cell F1 and the 200 in cell A3 must both be
numbers or both text. If one is a number and the other text you will get
#N/A. You can test for this with these formulas (in spare cells somewhere):
=ISTEXT(F1)
=ISTEXT(A3)

 

[Mervyn Edwards]

Thanks for the confirmation. This is really weird as I have checked all cells
that are referenced and they are all formatted as number, with no decimal
places. I changed the title row to numbers also i.e. 300, 310 & 320 but I am
still getting the "value not available" error with #N/A. I have also checked
there are values in each cell that is referenced.

Any ideas please.

Regards
Mervyn