Lookup a cell intercept?

  • Thread starter Thread starter john4938
  • Start date Start date
J

john4938

Here is my example:

I want to put zip codes in a column and then across in a row. I wan
to fill in the matrix with values after this. On another sheet, I wan
to put a zip from a row in one cell and a zip from a column in anothe
cell and have the value at their intercept display in another cell.
Not sure if this is even possible with Excel.

Any info would be great.

Thanks,

Joh
 
John,

If your table is in A1:J10, then you could use

=VLOOKUP(ZipToMatchColumnA,A1:J10,MATCH(ZipToMatchRow1,1:1,FALSE),FALSE)

where ZipToMatchColumnA and ZipToMatchRow1 can either be cell references or
values of interest.

HTH,
Bernie
MS Excel MVP
 
One way is to select the entire table, headers as well and do Insert / Name /
Create / Use names from Top & Left.

Now in any cell use =Zip1 Zip2 (Note the space between them)


or, don't worry about naming them and do

Hit Tools / Options / Calculation / Tick 'Accept labels in formulas'

Now in any cell use =Zip1 Zip2 (Note the space between them)


or, simply use a formula to get the data:-

Assuming your data is in A1:L10 and the zips you are looking up are in say C13
and C14, then:-

=INDEX($A$1:$L$10,MATCH($C$13,$A$1:$A$10,0),MATCH($C$14,$A$1:$L$1,0))


or with same ranges, but using OFFSET even:-

=OFFSET($A$1,MATCH($C$13,$A$2:$A$10,0),MATCH($C$14,$B$1:$L$1,0))

Don't really need all the $ signs, but I'm partial to them. :-)
 
Back
Top