Vlookup with Hlookup

  • Thread starter Thread starter Shona
  • Start date Start date
S

Shona

Is this possible I've had a go but obvioulsy not got it right

=VLOOKUP(EA1,$B$3:$B$423,1,0(HLOOKUP(A5,$B$3:$DY$423,3,0)))

Basically I want to look up a date in EA1 if it exists in the range then I
want to do a horizontal look up of a part number and put the number that
exists in the third row.

Any help would be much appreciated!

Thanks

Shona
 
Hi

=INDEX($C$4:$DY$423,MATCH($EA$1,$B$4:$B$423),MATCH($A$5,$C$3:$DY$3))
I assume column B and row 3 are headers/keys
Another way with same result
=OFFSET($B$3,MATCH($EA$1,$B$4:$B$423),MATCH($A$5,$C$3:$DY$3))
 
Thanks I think I'm nearly there but not quite I've reduced the size and here
is the general layout



A B C
D E F G

(column D empty) (column F empty)

06.01.04
7-700-352-952 7-702-340-006
Material No.
7-700-352-952 06.01.04 294 0 294
7-700-452-908 0 0 0
7-701-431-795 0 0 0
7-702-311-028 0 0 0
7-702-340-006 07.01.04 108 123 123
7-702-411-028 0 0
7-702-440-007 0 0
7-709-000-424 0 0



In G4 I have

=INDEX(B4:$E$11,MATCH($G$1,$B$4:$B$11),MATCH(A4,$B$2:$E$2)) which gives the
answer I would expect 294 but I would expect the rest to return 0. If I
change the date to G1 to 07.01.04 I would expect the 123 to be correct and
all the rest 0. For some unknown reasons not all of the zeros have not
right aligned


What am I doing wrong?

Cheers Shona
 
This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/xindexvba.zip
It's in the "Excel for Lotus 123 Users" section on page:
http://www.bygsoftware.com/examples/examples.htm

In Excel there is no direct equivalent for Lotus 123's XINDEX function. This
workbook shows two Excel formula constructions that achieve the same result.

The first example uses two additional inputs. It uses the Excel functions:
INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX
function. It uses the Excel functions: INDEX, MATCH and OFFSET.

There are also two additional pieces of VBA showing how to use this in code
and capture an error condition.

The code is open and commented.
--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
Hi

I couldn't read your layout from here, as it is somewhat messy.
The ranges B4:B11 and B2:E2 mustn't have any gaps in them, i.e. row/column
headers must be filled for whole range. The range you give as first
parameter is datarange WITHOUT row/column headers.
So I think the formula has to be something like
=INDEX(C4:E11;MATCH(G1;B4:B11;0);MATCH(A4;C2:E2;0))
When some numbers are left-aligned (with all alignements removed), then they
are text instead of numeric. To convert them to numbers:

1. way - format cells as numeric or general, activate edit mode for cell
(doubble-click, or F2, or point at end of number in formula bar) and press
Enter;
2. way - copy a number 1 from some cell, activate the range you want to
convert, and select PasteSpecial.Transpose.
 
Back
Top