0

Hy there,

i am trying to create an automatic invoice and probably i am making my life much more complicated than i should.

here is my problem... i have these tables (short version) Name Street Place

this i have of a variety of guests. on the invoice of the guest i want to just write the name and it automatically finds the adress and place of the specific guest in my tables.

in my excel file i am writing basically the name of the guest, in the street name i tried it through IF this name, vlookup in this table array. unfortunatly i dont know how i can say that when you find that name use this persons adress, which is below his name... at least i couldnt manage that it shows the correct adress if the person i typed in, just that it shows the name again or the adress of the first person in the list.

hope it is half way clear and someone has an easy solution for me :)

4
  • 1
    Please share the IF/VLOOKUP formula you are using so we can help you resolve it.
    – CharlieRB
    Jan 23, 2014 at 12:59
  • 1
    The formula would be as follow (the cells are just named randomly) if(A1=A2,vlookup(A1,B1:B4),1,false),false) best would be if the table array could be more columns, so i assume macth as mentioned by bensheperd would be more of help here. trying to work that out at that mment with match and offset combined
    – Bardrik
    Jan 23, 2014 at 13:29
  • Is your data like this - (row) (1)name (2)street (3)place (4)name2 (5)street2 (6)place2 ? Jan 23, 2014 at 13:53
  • hey, i could arrange it under each other, but so far it is as follows: column 1 Row1 name column 1 Row2 street column 1 Row3 place (5 timesbelow each other with empty spaces in between)and than in column 4 the next guests, same way as before
    – Bardrik
    Jan 23, 2014 at 13:57

3 Answers 3

1

You can use a simple index match that is offset by the number of rows (1 here) like -

=INDEX($D$1:$D$6,MATCH(A1,$D$1:$D$6,0)+1)

enter image description here

1
  • this works perfectly :) thank you so much... saves me from typing hundreds of adress manually :)
    – Bardrik
    Jan 23, 2014 at 14:22
0

Some combination of MATCH (to find the row you're looking for) and OFFSET should do the trick. That should give you a more generalised version of the VLOOKUP that you're currently using.

1
  • Hy Benshepherd, thanks for the help so far. unfortunatly i dont get the offset work as i want. cyrrently i have offset(Match(NameCell,A38:N86),0),1,0,1,1) it tells me the formula is not valid. is it possible to use the match as search fo the offset?
    – Bardrik
    Jan 23, 2014 at 13:55
0

You've not explained how the data is arranged, so I can not be sure if this will be correct, but I believe your VLOOKUP syntax is wrong within the IF statement.

Try this;

=IF(A1=A2,VLOOKUP(A1,B1:B4,1,false),"false")

This will check if A1 is equal to A2. If it is it will look up an exact match of the value of A1 in the range of B1:B4, then return the result of the first column. In the case where the IF finds A1 is not equal to A2, it will return the text "false".

Here is a great tutorial on vlookup(), match() and offset() – explained in plain english [spreadcheats].

2
  • Hey CharlieRB, this is exactly what it does... just that i do not want to display the value in the cell or next to the cell found but the ones below. and it would be necessary that this cell filled in is always from the found value... it is arranged in my file as mentioned int he starting post comments... ( column 1 Row1 name column 1 Row2 street column 1 Row3 place (5 timesbelow each other with empty spaces in between)and than in column 4 the next guests, same way as before
    – Bardrik
    Jan 23, 2014 at 14:05
  • Now that we know how the data is laid out, VLOOKUP is not useful for you. @raystafarian has a great answer for you above.
    – CharlieRB
    Jan 23, 2014 at 14:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .